#!/bin/bash

#
#  @auther: deep as the sea
#  @qq: 83544844
#  @wx: doit_edu
#  @date: 2021-09-21
#  @des: 空设备guid映射表更新计算
#
#

export HIVE_HOME=/opt/apps/hive

dt=`date -d'-1 day' +%Y-%m-%d`


if [ $# -gt 0 ];then
dt=$1
fi

pre=`date -d"-1 day ${dt}" +%Y-%m-%d`


echo "INFO:  空设备guid映射表更新计算任务开始，id映射表(${pre}) + 绑定评分表(${dt})  => id映射表(${dt})"

# 可以在这里先执行一个sql来获取T-1日的最大guid
# max_guid=` TODO `

${HIVE_HOME}/bin/hive -e "
set hive.mapred.mode=nonstrict;
set hive.strict.checks.cartesian.product=false;

WITH null_device as (
-- SELECT
--   device_id
-- FROM dws.mall_app_dev_bind
-- WHERE dt='${dt}'
-- GROUP BY device_id
-- HAVING max(account) is NULL

SELECT
 device_id
FROM dws.mall_app_dev_bind_f
WHERE dt='${dt}' AND account is null

),
pre_idmp as(
SELECT
 device_id,
 guid
FROM dws.device_guid_idmp 
WHERE dt='${pre}'
)


INSERT INTO TABLE dws.device_guid_idmp PARTITION(dt='${dt}')
SELECT
null_device.device_id  ,
pre_idmp.guid 
FROM  null_device join pre_idmp  ON null_device.device_id=pre_idmp.device_id

UNION ALL

SELECT
  o1.device_id,
  o1.rn+max_pre as guid
FROM 
(
   SELECT
    null_device.device_id,
    row_number() over() as rn
   FROM  null_device left join pre_idmp  ON null_device.device_id=pre_idmp.device_id
   WHERE pre_idmp.guid is NULL
) o1

JOIN

(
  SELECT  nvl(max(guid),10000000) as max_pre from pre_idmp
) o2 



"

if [ $? -eq 0 ];then
echo "dw task sucessed :设空设备guid映射表更新计算任务成功，id映射表(${pre}) + 绑定评分表(${dt})  => id映射表(${dt})" | mail -s '数易平台任务运行成功通知' 83544844@qq.com
else
echo "dw task failed 空设备guid映射表更新计算任务失败，id映射表(${pre}) + 绑定评分表(${dt})  => id映射表(${dt})" | mail -s '数易平台任务运行失败通知' 83544844@qq.com
fi
